# -*- coding: utf-8 -*-


from db import *
from time import strftime, localtime

class SaveData:
    def __init__(self, wizdata):
        self.data = wizdata
        pass

    def saveNewCol(self):
        """
        Error 1364: Field 'col_name' doesn't have a default value

            insert into aid_columns (
            name, table_, dir, col_type, description, params, ordering, published, checked_out, checked_out_time, createdon, createdby)
            values('name','aid_items', 1, 1, 'description', '', 1, 1, 0, '0000-00-00', now(),'admin')
            
        Error 1364: Field 'col_name' doesn't have a default value

            insert into aid_columns (
            name, table_, dir, col_type, description, params, ordering, published, checked_out, checked_out_time, createdon, createdby)
            values('name2','aid_items', 1, 1, 'description', '', 1, 1, 0, '0000-00-00', now(),'admin')
        
        """
        
        
        #data = wizdata.WizData()
        

        
        #data.collist = [{'name':'name','table_':'aid_items','dir':1},{'name':'name2','table_':'aid_items','dir':1}]
        now = strftime("%Y-%m-%d %H:%M:%S", localtime())
        for colinfo in self.data.newcollist:
            sql = """
            insert into aid_columns (
            name, table_, col_name, keycol, dir, col_type, description, params, ordering, published, checked_out, checked_out_time, createdon, createdby)
            values('%s','%s', '%s', 0, %s, 1, 'desc', '', 1, 1, 1, '0000-00-00', '%s','admin')
            """ %(colinfo.name, colinfo.table_, colinfo.name, now, colinfo.dir)
            #print sql.encode('utf8')
            state = db.execute(sql)            
            #self.multiText.AppendText("save new column[%s]\n"%(state))
        
    def saveExcel(self):
        #tdb = db.db()

        #data = wizdata.WizData() 
        file = self.data.excelfile
        #file.filename = file.filename.replace('\\','\\\\')
        
        file.map_uid = self.data.map.uid # set when tree node update
        
        file.period = self.data.period # set when show excel file data
        file.pstart = self.data.pstart # set when show excel file data
        file.pend = self.data.pend     # set when show excel file data
        file.inout_date = self.data.inout
        
        file.createdby = 'admin01'
        #db = DB()
        self.file_uid = db.setFile(file)
        
        
        sql = 'select id, name, table_, col_name from aid_columns where published = 1 '
        result_set = db.execute(sql)
        #print result_set
        
        #cd = getcol.GetColumns()
        
        cinfo = {}
        for item in result_set:
            if type(item.name) == unicode:
                name = item.name.encode('utf8')
            else:
                name = item.name
            #del item.name
            cinfo[name] = {'id':item.id, 'table_':item.table_, 'col_name':item.col_name}
        
        #print cinfo
        #print cinfo
        #cinfo = cd.getcolinfo()
        
        
        #cinfo = db.getColNames()
        
        #print cinfo
        #return 1
        #print cinfo
        #data.key_col = data.key_col.encode('utf8')
        #tab =  cinfo[data.key_col]['table_']
        
        #print data.key_col
        
        if type(self.data.key_col) == unicode:
            self.data.key_col = self.data.key_col.encode('utf8')
            
        column = cinfo[self.data.key_col]['col_name'] 
        
        #print tab
        #print column
        
        exec("mapping =" +self.data.mapping)
        i = -1
        emp = []
        item = []
        uid = []
        ikey = None
        col = None
        #print mapping
        columns = ""
        for x in mapping:  # classification the map item
            
            i = i +1
            if x == "---":
                continue
            else:
                if cinfo[x]['table_'] == 'aid_employees':
                    #print "x: %s"%(x)
                    #print type(data.key_col)
                    #print "key_col: %s" %(data.key_col)
                    """
                    if isinstance( x, str):
                        print x
                    if isinstance( data.key_col, str):
                        print "key_col is unicode"
                    """
                    if x == self.data.key_col:
                        ikey = i                        
                        col = cinfo[x]['col_name']
                    columns = columns + cinfo[x]['col_name'] +","
                    emp.append(i)
                else:
                    item.append(i)
        ikey = self.data.colstart + ikey
        columns = columns+ " uid, createdon, access, oid "
        oid = 0
        for y in range(self.data.rowstart+1, self.data.nrows-self.data.lastline):
            oid = oid +1
            item_dict = {}
            #print data.exceldata[y][ikey]
            
            # get employee from db
            
            uid_sql  = "select uid as uid from aid_employees where %s = '%s'" %(column, self.data.exceldata[y][ikey])
            #print uid_sql
            empinfo = db.execute(uid_sql)
            
            one = empinfo.fetchone()
            print one
            #print type(one)
            if  one != None:  # return true if fetch something?  by mjj                
                self.employee_uid = one[0]
                self.emp_exist = True
                #print self.employee_uid
            else:
               self.employee_uid = uuid() 
               self.emp_exist = False
                #print uid_sql
            
            values = ""
            now = strftime("%Y-%m-%d %H:%M:%S", localtime())
            for i in range(self.data.colstart, self.data.ncols):
                if emp.count(i - self.data.colstart) != 0:
                    values = values +"'" + self.data.exceldata[y][i] + "',"
                    #print i
                elif item.count(i-self.data.colstart)!= 0:
                    item_dict[mapping[i-self.data.colstart]] = self.data.exceldata[y][i]
            
            if self.emp_exist:
                pass
            else:
                values = values + " '" + self.employee_uid +"', '"+now+"', 1"
                sql = "insert into aid_employees (%s) values(%s, %s)" %(columns, values, oid) 
                db.execute(sql)
                
            for k in item_dict.keys():
                
                
                
                sql = "insert into aid_items (colname, value, employee_uid, file_uid, row_no, createdon) \
                    values('%s', %s, '%s', '%s', %s, '%s')" \
                        %(k.decode('utf8'), item_dict[k], self.employee_uid, self.file_uid, y+1, now) 
                # y + 1 , for user, line start from 1
                #print type(sql)
                #sql = sql.encode('utf8')
                db.execute(sql)
            
        
    def saveNewMap(self):
        
        #data = wizdata.WizData()
        
        self.data.map.uid = uuid()
        
        labels_str = "["
        maplist_str = "["
        end = ","
        ilen = len (self.data.labels)
        
        for i  in range(ilen):
            if i == ilen -1:
                end = "]"
            labels_str = labels_str + '"' + self.data.labels[i]  +'"' +  end
            maplist_str = maplist_str +'"' +  self.data.list[i] +'"' +  end         
 
        self.data.mapping = maplist_str
        now = strftime("%Y-%m-%d %H:%M:%S", localtime())
        sql = "insert into aid_mapping (mappingtype,name,uid,col_list,mapping,key_col, start_row,start_col,ignore_last,createdon,createdby,access) \
                values('%s', '%s', '%s', '%s', '%s', '%s',%s, %s, %s, '%s', '%s', %s )" %(self.data.mappingtype, self.data.map_name, 
                    self.data.map.uid, labels_str, maplist_str, self.data.key_col, self.data.rowstart, self.data.colstart, self.data.lastline, now, u'admin',1)
        map_uid = db.execute(sql)        
        print "save new map[%s]\n"%(map_uid)



